import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from SKO.AbstractDPJob import AbstractDPJob
class Predict_6623Job(AbstractDPJob):
    def __init__(self,
                 p_create_date=None):
        super(Predict_6623Job, self).__init__()
        self.create_date = p_create_date


        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):
        super(Predict_6623Job, self).do_execute()
        #预测铁水硫接口传入参数
        msg = ''
        result_list = []
        create_date = self.create_date
        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'm1admin'
        DB_PASSWORD_MPP_DB2 = 'm1adminbdzg'

        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        # db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
        #                                DB_PORT_MPP_DB2,
        #                                DB_DBNAME_MPP_DB2,
        #                                DB_USER_MPP_DB2,
        #                                DB_PASSWORD_MPP_DB2)
        # 煤种使用数量及价格
        # sql = " SELECT PROD_CODE,WT,AD_WT " \
        #       " FROM BG00MAZZAI.T_ADS_WH_YLMX_COKE_PLAN3  " \
        #       " WHERE PROD_DATE='%s' " % (create_date)
        # print(sql)
        # df51 = pd.read_sql_query(sql, con=db_conn_mpp)
        # df51.columns = df51.columns.str.upper()
        # df51.WT.fillna(0, inplace=True)
        # df51.AD_WT.fillna(0, inplace=True)
        # df51_new = df51[(df51['WT'] > 0) | (df51['AD_WT'] > 0)]
        # sql = " SELECT PROD_CODE,PRICE " \
        #       " FROM BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE3  " \
        #       " WHERE PROD_DATE='%s' " % (create_date)
        # print(sql)
        # df52 = pd.read_sql_query(sql, con=db_conn_mpp)
        # df52.columns = df52.columns.str.upper()
        # v = ['PROD_CODE']
        # df_5_new = pd.merge(df51_new, df52, on=v, how='left')
        xlsx_name = 'D:/repos/sicost/COKE2使用数量.xlsx'
        df5 = pd.read_excel(xlsx_name)
        df5.columns = df5.columns.str.upper()
        df5.WT.fillna(0, inplace=True)
        df5.AD_WT.fillna(0, inplace=True)
        df5_new = df5[(df5['WT'] > 0) | (df5['AD_WT'] > 0)]
        df5_new = df5_new.reset_index(drop=True)


        # 煤质

        xlsx_name = 'D:/repos/sicost/COKE2煤质.xlsx'
        df6 = pd.read_excel(xlsx_name)
        df6.columns = df6.columns.str.upper()
        df6.drop(['VAR'], axis=1, inplace=True)
        df6.drop(['CLASS'], axis=1, inplace=True)
        df6.drop(['SOURCE'], axis=1, inplace=True)
        df6.drop(['PROD_DSCR'], axis=1, inplace=True)
        df6.drop(['H2O'], axis=1, inplace=True)
        df6.drop(['ASH'], axis=1, inplace=True)
        df6.drop(['COKING_COALBLD_BOND_IND'], axis=1, inplace=True)
        df6.drop(['M_L3_JZCHD_Y'], axis=1, inplace=True)
        df6.drop(['COKING_COALBLD_GIESFLU'], axis=1, inplace=True)
        df6.drop(['L_M_AB'], axis=1, inplace=True)
        df6.drop(['C'], axis=1, inplace=True)
        df6.drop(['COKE_HOTVALUE'], axis=1, inplace=True)
        # sql = " SELECT CREATE_DATE,PROD_CODE,COKE_VM,S " \
        #       " FROM BG00MAZZAI.T_ADS_WH_YLMX_COKE_IND_INFO3  " \
        #       " WHERE PROD_DATE='%s' " % (create_date)
        # print(sql)
        # df6 = pd.read_sql_query(sql, con=db_conn_mpp)
        v = ['PROD_CODE']
        df_56 = pd.merge(df5_new, df6, on=v, how='left')
        df_56['TOTAL_COKE_VM'] = df_56['COKE_VM'] * df_56['WT']
        df_56['TOTAL_S'] = df_56['S'] * df_56['WT']
        df_56['TOTAL_PRICE'] = df_56['PRICE'] * df_56['WT']
        df_56['AD_TOTAL_COKE_VM'] = df_56['COKE_VM'] * df_56['AD_WT']
        df_56['AD_TOTAL_S'] = df_56['S'] * df_56['AD_WT']
        df_56['AD_TOTAL_PRICE'] = df_56['PRICE'] * df_56['AD_WT']

        total_wt = df_56['WT'].sum()
        ad_total_wt = df_56['AD_WT'].sum()
        total_s = df_56['TOTAL_S'].sum()
        ad_total_s = df_56['AD_TOTAL_S'].sum()
        total_vm = df_56['TOTAL_COKE_VM'].sum()
        ad_total_vm = df_56['AD_TOTAL_COKE_VM'].sum()
        total_price = df_56['TOTAL_PRICE'].sum()
        ad_total_price = df_56['AD_TOTAL_PRICE'].sum()
        old_unit_price = total_price / total_wt
        new_unit_price = ad_total_price / ad_total_wt
        old_s = total_s / total_wt
        new_s = ad_total_s / ad_total_wt
        old_vm = total_vm / total_wt
        new_vm = ad_total_vm / ad_total_wt
        # sql = " select PARM_CHN,PARM_CALC " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_COEF2 " \
        #       " where TMPL_NO ='original' "
        # data_jiaotancanshu = pd.read_sql_query(sql, con=db_conn_mpp)
        data_jiaotancanshu = pd.read_excel('COKE2参数.xlsx')
        data_jiaotancanshu.columns = data_jiaotancanshu.columns.str.upper()
        parm_4 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_4')]
        parm_4 = parm_4['PARM_CALC'].values[0]
        parm_5 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_5')]
        parm_5 = parm_5['PARM_CALC'].values[0]
        parm_6 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_6')]
        parm_6 = parm_6['PARM_CALC'].values[0]
        old_jiaotan_s = parm_4 + parm_5 * (old_s) / (100 - old_vm) - parm_6 * (old_vm)
        new_jiaotan_s = parm_4 + parm_5 * (new_s) / (100 - new_vm) - parm_6 * (new_vm)

        df_out = pd.DataFrame(columns=['PLAN_NAME', 'COKE_VM', 'S', 'UNIT_PRICE', 'COKING_COKE_SULCONT'])
        dict = {}

        dict['PLAN_NAME'] = '调整炼焦煤配比前'
        dict['COKE_VM'] = old_vm
        dict['S'] = old_s
        dict['UNIT_PRICE'] = old_unit_price
        dict['COKING_COKE_SULCONT'] = old_jiaotan_s
        new_row = pd.Series(dict)
        df_out = df_out.append(new_row, ignore_index=True)
        dict = {}

        dict['PLAN_NAME'] = '调整炼焦煤配比后'
        dict['COKE_VM'] = new_vm
        dict['S'] = new_s
        dict['UNIT_PRICE'] = new_unit_price
        dict['COKING_COKE_SULCONT'] = new_jiaotan_s
        new_row = pd.Series(dict)
        df_out = df_out.append(new_row, ignore_index=True)
        msg = '运行成功'
        result_list = df_out.to_dict(orient='records')


        print('finish')
        return msg, result_list

